import sys
import psycopg2.pool,psycopg2.extensions,copy,traceback,datetime,decimal,json
from logging import getLogger
# from doc_util import *

#<class 'odoo.sql_db.Cursor'>

#psycopg2.extensions.connection
class DBConnection(psycopg2.pool.SimpleConnectionPool):
    def __init__(self,pool):
        self._pool=pool
        self._con=pool.getconn()

    def __enter__(self): return self
    def __exit__(self,exc_type,exc_value,exc_tb):
        try:
            if exc_tb==None:
                self._con.commit()
            else:
                self._con.rollback()
                print(str(exc_tb))

        finally:
            #self._con.close()
            self._pool.putconn(self._con)

        return exc_tb==None


    def cursor(self,*kw,**kwargs): return DBCursor(self._con.cursor())


class DBCursor(psycopg2.extensions.cursor):

    def __init__(self,cursor):
        self._cur=cursor

    def __enter__(self):
        return self

    def __exit__(self,exc_type,exc_value,exc_tb):
        tb_format=traceback.format_exception(exc_type,exc_value,exc_tb)
        #getLogger().exception(tb_format)
        self._cur.close()
        return exc_tb==None

    def db2python(self,o,type_code,is_parse_json=0):
        r=o
        if isinstance(o,datetime.datetime):r=o.strftime("%Y-%m-%d %H:%M:%S")
        elif isinstance(o,datetime.date):r=o.strftime("%Y-%m-%d")
        elif (isinstance(o,datetime.timedelta)):r=str(o)
        elif isinstance(o,decimal.Decimal):
            try:r=int(o)
            except:r=float(o)
        elif isinstance(o,str):
            if type_code==1114:r=o[:-7]


            if o.__len__()>=2 and is_parse_json==1:
                if (o[0]=='[' and o[-1]==']') or (o[0]=='{' and o[-1]=='}'):r=json.loads(o)
        return r

    def execute(self, *kw,**kwargs):return self._cur.execute(*kw,**kwargs)

    def executemany(self, *kw,**kwargs):return self._cur.executemany(*kw,**kwargs)


    def tr(self,*kw,**kwargs):
        self.execute(*kw,**kwargs)
        return self.otr()

    def otr(self):
        title=[]
        for o in self._cur.description:
            title.append(o[0])
        rows=[]
        row=self._cur.fetchone()
        while row:
            r=list(row)
            for i in range(len(r)):
                o=r[i]
                r[i]=self.db2python(o,self._cur.description[i].type_code)
            rows.append(r)
            row=self._cur.fetchone()

        return {'title':title,'rows':rows}

    def list(self,*kw,**kwargs):
        self.execute(*kw,**kwargs)
        return self.olist()

    def olist(self):
        title_rows=self.otr()
        title,rows=title_rows['title'],title_rows['rows']
        r=[]
        for o in rows:
            a={}
            for i in range(0,title.__len__()):
                a[title[i]]=o[i]
            r.append(a)
        return r

    def col(self,*kw,**kwargs):
        self.execute(*kw,**kwargs)
        return self.ocol()

    def ocol(self):
        rows=[]
        row=self._cur.fetchone()
        while row:
            r=list(row)
            for i in range(len(r)):
                o=r[i]
                r[i]=self.db2python(o,self._cur.description[i].type_code)
            rows.append(r)
            row=self._cur.fetchone()
        r=[]
        for o in rows:
            r.append(o[0])
        return r

    def insert(self,*kw,**kwargs):
        self.execute(*kw,**kwargs)
        try:
            return int(self._cur.lastrowid)
        except:
            return -1

    def delete(self,*kw,**kwargs):
        self.execute(*kw,**kwargs)
        return self._cur._rowcount

    def update(self,*kw,**kwargs):
        self.execute(*kw,**kwargs)
        return self._cur._rowcount

    def to_db(self,o,table):
        col_list=self.col("SELECT column_name FROM Information_schema.columns WHERE table_name='%s'"%table)
        r={}
        for k in o:
            if k in col_list:
                r[k]=o[k]
        return r

    def init_dict(self,table,odoo=0):
        r={}
        col_list=self.col("SELECT column_name FROM Information_schema.columns WHERE table_name='%s'"%table)
        for col in col_list:
            if col=='id':continue
            r[col]=None

        if odoo==0:
            kl=['create_uid','create_date','write_uid','write_date']
            for k in kl:
                if k in r:del r[k]
        return r



    def insert_one(self,o,table,contains_id=1,return_id=1):

        col_list=self.col("SELECT column_name FROM Information_schema.columns WHERE table_name='%s'"%table)

        if contains_id==0:col_list.remove('id')
        key_list=[k for k in o]
        col_list=list(filter(lambda col:col in key_list,col_list))
        #rows=[[None for i in range(col_list.__len__())] for j in range(l.__len__())]




        one=[None for i in range(col_list.__len__())]

        for k in o:
            if k in col_list:
                one[col_list.index(k)]=o[k]


        return_sql='' if return_id==0 else 'RETURNING ID'


        sql="INSERT INTO %s %s VALUES %s %s"%(table,'(%s)'%(','.join(col_list)),'(%s)'%(','.join(['%s' for i in range(col_list.__len__())])),return_sql)
        self.execute(sql,one)
        if return_id==1:
            return self._cur.fetchone()[0]


    def insert_list(self,l,table,col_list=None,contains_id=0):
        if l.__len__()==0:return
        if col_list==None:col_list=self.col("SELECT column_name FROM Information_schema.columns WHERE table_name='%s'"%table)

        if contains_id==0 and 'id' in col_list:col_list.remove('id')
        key_list=[k for k in l[0]]
        col_list=list(filter(lambda col:col in key_list,col_list))
        #rows=[[None for i in range(col_list.__len__())] for j in range(l.__len__())]

        rows=[]

        for o in l:
            row=[None for i in range(col_list.__len__())]

            for k in o:
                if k in col_list:
                    if type(o[k])!=dict:
                        row[col_list.index(k)]=o[k]
                    else:row[col_list.index(k)]=json.dumps(o[k],ensure_ascii=0)
            rows+=[row]



        sql="INSERT INTO %s %s VALUES %s"%(table,'(%s)'%(','.join(col_list)),'(%s)'%(','.join(['%s' for i in range(col_list.__len__())])))
        self.executemany(sql,rows)


    def save_list(self,l,table):

        try:
            id_list=[str(o['id']) for o in l]
            self.execute("DELETE FROM %s WHERE id IN %s"%(table,'('+','.join(id_list)+')'))
        except:pass
        self.insert_list(l,table)


    def insert_many(self,value_list,table,col_list=None,copy_id=0):
        if col_list==None:col_list=self.col("SELECT column_name FROM Information_schema.columns WHERE table_name='%s'"%table)

        if copy_id==0 and 'id' in col_list:col_list.remove('id')
        s=('%s,'*col_list.__len__())[:-1]
        sql="INSERT INTO %s (%s) VALUES (%s)"%(table,','.join(col_list),s)
        self.executemany(sql,value_list)

    def update_one(self,o,table):

        kv=''
        for k in o:
            if k!='id':
                kv+=k+'='+o[k]+','


        sql="UPDATE %s SET %s WHERE id=%d"%o['id']








